# Create your views here.
import pymysql
from django.db import connection
from django.shortcuts import render,redirect,HttpResponse
from Max import models
from Max.models import Job


def home(request):
    myusername=request.user.username
    # ret = models.Job.objects.all()
    # conObj = pymysql.connect(host='localhost', port=3306, user='root', password='', db='employ1', charset='utf8')

    cursor = connection.cursor()
    cursor.execute("select max(pay) from job group by tid order by tid asc")
    max_price=cursor.fetchall()

    cursor.execute("select avg(pay) from job group by tid order by tid asc")
    # raw = cursor.fetchone()  # 返回结果行游标直读向前，读取一条
    avg_price = cursor.fetchall()#读所有

    maxpricelist=[]
    for i in max_price:
        for j in i:
            maxpricelist.append(j)
    # print(maxpricelist)

    avgpricelist = []
    for i in avg_price:
        for j in i:
            avgpricelist.append(j)
    # print(avgpricelist[0])

    job_sumlist = []
    cursor.execute("select count(*) from job group by tid")
    job_sum = cursor.fetchall()
    for i in job_sum:
        for j in i:
            job_sumlist.append(j)
    # print(job_sumlist)

    cursor.execute("select count(*) from job where pay>"+str(avgpricelist[0])+" and tid=1")
    java_cou=cursor.fetchone()[0]

    cursor.execute("select count(*) from job where pay>" + str(avgpricelist[1]) + " and tid=2")
    web_cou = cursor.fetchone()[0]

    cursor.execute("select count(*) from job where pay>" + str(avgpricelist[2]) + " and tid=3")
    php_cou = cursor.fetchone()[0]

    cursor.execute("select count(*) from job where pay>" + str(avgpricelist[3]) + " and tid=4")
    python_cou = cursor.fetchone()[0]

    cursor.execute("select count(*) from job where pay>" + str(avgpricelist[4]) + " and tid=5")
    android_cou = cursor.fetchone()[0]


    java_ba = round(((maxpricelist[0]-avgpricelist[0])/maxpricelist[0])*100,3)
    web_ba = round(((maxpricelist[1] - avgpricelist[1]) / maxpricelist[1])*100,3)
    php_ba = round(((maxpricelist[2] - avgpricelist[2]) / maxpricelist[2])*100,3)
    python_ba = round(((maxpricelist[3] - avgpricelist[3]) / maxpricelist[3])*100,3)
    android_ba = round(((maxpricelist[4] - avgpricelist[4]) / maxpricelist[4])*100,3)

    java_couba=round((java_cou/job_sumlist[0])*100,2)
    web_couba = round((web_cou/job_sumlist[1])*100,2)
    php_couba = round((php_cou/job_sumlist[2])*100,2)
    python_couba = round((python_cou/job_sumlist[3])*100,2)
    android_couba = round((android_cou/job_sumlist[4])*100,2)
    # print(java_couba,web_couba,php_couba,python_couba,android_couba)

    return render(request,'index.html',{'max_javaprice':maxpricelist[0],'max_webprice':maxpricelist[1],'max_phpprice':maxpricelist[2],'max_pythonprice':maxpricelist[3],'max_androidprice':maxpricelist[4],
                                        'avg_javaprice':avgpricelist[0],'avg_webprice':avgpricelist[1],'avg_phpprice':avgpricelist[2],'avg_pythonprice':avgpricelist[3],'avg_androidprice':avgpricelist[4],
                                        'java_ba':java_ba,'web_ba':web_ba,'php_ba':php_ba,'python_ba':python_ba,'android_ba':android_ba,
                                        'java_cou':java_cou,'web_cou':web_cou,'php_cou':php_cou,'python_cou':python_cou,'android_cou':android_cou,
                                        'java_sum': job_sumlist[0],'web_sum': job_sumlist[1],'php_sum': job_sumlist[2],'python_sum': job_sumlist[3],'android_sum': job_sumlist[4],
                                        'java_couba':java_couba,'web_couba':web_couba,'php_couba':php_couba,'python_couba':python_couba,'android_couba':android_couba
                                        ,'username':myusername} )

# 搜索模块 by wwt
#select AVG(pay),MAX(pay),MIN(pay),j.tid,typename from job j JOIN type t on j.tid=t.tid  where region LIKE "%上海%" GROUP BY tid
def do_search(request):
    search = request.GET['search']
    search_list=[]
    cursor = connection.cursor()
    cursor.execute('''select j.tid,typename,MIN(pay),
                      round(AVG(pay),2), MAX(pay),COUNT(id) 
                      from job j JOIN type t on j.tid=t.tid  
                      where region LIKE '%%%s%%' GROUP BY tid'''%search)
    data = cursor.fetchall()
    for i in data:
        search_list.append(i)
    print(search_list)
    return render(request,'search_table.html',{"search_list":search_list})
#end
